package com.wf.ew.system.biz.impl;

import com.wf.ew.common.BaseDao;
import com.wf.ew.common.PageResult;
import com.wf.ew.common.constant.UserConstant;
import com.wf.ew.common.utils.Utils;
import com.wf.ew.system.biz.UserBiz;
import com.wf.ew.system.model.LoginRecord;
import com.wf.ew.system.model.User;
import com.wf.ew.system.model.UserRole;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * 数据层实现类
 * @author : Doman
 * @date : 2019-09-28 23:36:08
 * @version ：1.0
 */
@Repository
public class UserBizimpl extends BaseDao implements UserBiz {

	@Override
	public String list(PageResult<T> page, User user, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<User> rm = new BeanPropertyRowMapper<User>(User.class);
		
		StringBuffer sql = new StringBuffer("select u.id, u.companyId, u.name, u.password, u.nickName,"
			+" u.avatar, u.sex, u.phone, u.email, u.idCard,"
			+" u.birthday, u.deptId, u.state, u.createTime, u.createUser,"
			+" u.updateTime, u.updateUser, ");
		sql.append(" (select group_concat(ur.role_id order by ur.role_id separator ',') as roles " +
				" from sys_user_role ur " +
				" inner join sys_role sr on sr.role_id=ur.role_id " +
				" where ur.user_id=u.id " +
				" group by ur.user_id ) roleIds, ");
		sql.append(" (select group_concat(sr.role_name order by sr.role_id separator ',') as roleNames " +
				" from sys_user_role ur " +
				" inner join sys_role sr on sr.role_id=ur.role_id " +
				" where ur.user_id=u.id " +
				" group by ur.user_id ) roleNames ");
		sql.append(",cmy.name companyName,suser.name createName ");
		sql.append(" from sys_user u ");
		sql.append(" inner join sys_user suser on suser.id=u.createUser ");
		sql.append(" inner join we_company cmy on cmy.id=u.companyId ");
		sql.append(" where u.del=0 ");
		if(!loginUser.getId().equals(UserConstant.ADMIN_ID)){
            sql.append(" and u.companyId=:tablecompanyId ");
            map.put("tablecompanyId", loginUser.getCompanyId());
        }
		if(Utils.isNotEmpty(user)){
//			if(Utils.isNotEmpty(user.getName())){
//				sql.append(" and instr(u.name,:name)>0 ");
//				map.put("name", user.getName());
//			}
			if(Utils.isNotEmpty(user.getSort())&&Utils.isNotEmpty(user.getOrder())){
				sql.append(" order by "+user.getSort()+" "+user.getOrder());
			}
		}
		return jsonPage(getPageQueryListNotOrderBy(page, sql.toString(), map, rm));
	}

	@Override
	public List<User> list(User user, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<User> rm = new BeanPropertyRowMapper<User>(User.class);

		StringBuffer sql = new StringBuffer("select u.id, u.companyId, u.name, u.password, u.nickName,"
				+" u.avatar, u.sex, u.phone, u.email, u.idCard,"
				+" u.birthday, u.deptId, u.state, u.createTime, u.createUser,"
				+" u.updateTime, u.updateUser,");
		sql.append(" (select group_concat(ur.role_id order by ur.role_id separator ',') as roles " +
				" from sys_user_role ur " +
				" inner join sys_role sr on sr.role_id=ur.role_id " +
				" where ur.user_id=u.id " +
				" group by ur.user_id ) roleIds, ");
		sql.append(" (select group_concat(sr.role_name order by sr.role_id separator ',') as roleNames " +
				" from sys_user_role ur " +
				" inner join sys_role sr on sr.role_id=ur.role_id " +
				" where ur.user_id=u.id " +
				" group by ur.user_id ) roleNames ");
		sql.append(",cmy.name companyName,suser.name createName ");
		sql.append(" from sys_user u ");
		sql.append(" inner join sys_user suser on suser.id=u.createUser ");
		sql.append(" inner join we_company cmy on cmy.id=u.companyId ");
		sql.append(" where u.del=0 ");
		if(!loginUser.getId().equals(UserConstant.ADMIN_ID)){
			sql.append(" and u.companyId=:tablecompanyId ");
			map.put("tablecompanyId", loginUser.getCompanyId());
		}
		if(Utils.isNotEmpty(user)){
//			if(Utils.isNotEmpty(user.getName())){
//				sql.append(" and instr(u.name,:name)>0 ");
//				map.put("name", user.getName());
//			}
			if(Utils.isNotEmpty(user.getSort())&&Utils.isNotEmpty(user.getOrder())){
				sql.append(" order by "+user.getSort()+" "+user.getOrder());
			}
		}
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public List<UserRole> listUserRole(User user) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<UserRole> rm = new BeanPropertyRowMapper<UserRole>(UserRole.class);

		StringBuffer sql = new StringBuffer("select ur.id,ur.user_id userId,ur.role_id roleId, r.role_name roleName,ur.createTime " +
				" from sys_user_role ur " +
				" inner join sys_role r on r.role_id=ur.role_id ");
		sql.append(" where ur.user_id=:userId ");
		map.put("userId", user.getId());
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}
    @Override
	public List<User> userNameUnique(User user) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<User> rm = new BeanPropertyRowMapper<User>(User.class);
        StringBuffer sql = new StringBuffer("select u.id ");
        sql.append(" from sys_user u ");
        sql.append(" where u.del=0 and u.name=:name");
        map.put("name", user.getName());
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public User getById(User user) {
		RowMapper<User> rm = new BeanPropertyRowMapper<User>(User.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select u.id, u.companyId, u.name, u.password, u.nickName,"
			+" u.avatar, u.sex, u.phone, u.email, u.idCard,"
			+" u.birthday, u.deptId, u.state, u.createTime, u.createUser,"
			+" u.updateTime, u.updateUser, ");
		sql.append(" (select group_concat(ur.role_id order by ur.role_id separator ',') as roles " +
				" from sys_user_role ur " +
				" inner join sys_role sr on sr.role_id=ur.role_id " +
				" where ur.user_id=u.id " +
				" group by ur.user_id ) roleIds, ");
		sql.append(" (select group_concat(sr.role_name order by sr.role_id separator ',') as roleNames " +
				" from sys_user_role ur " +
				" inner join sys_role sr on sr.role_id=ur.role_id " +
				" where ur.user_id=u.id " +
				" group by ur.user_id ) roleNames ");
			sql.append(",cmy.name companyName,suser.name createName ");
			sql.append(" from sys_user u ");
			sql.append(" inner join sys_user suser on suser.id=u.createUser ");
			sql.append(" inner join we_company cmy on cmy.id=u.companyId ");
        sql.append(" where u.del=0 ");
		sql.append("and u.id=:id ");
		map.put("id", user.getId());
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}

	@Override
	public KeyHolder add(User user, User loginUser) {
		int count = 0;
		KeyHolder keyHolder=new GeneratedKeyHolder();
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("insert into sys_user (companyId,name,password,nickName "
			+",avatar,sex,phone,email,idCard "
			+",birthday,deptId,state,createUser "
			+" )");
		sql.append("values (:companyId,:name,:password,:nickName "
			+",:avatar,:sex,:phone,:email,:idCard "
			+",:birthday,:deptId,:state,"+loginUser.getId()
			+" )");
		map.put("companyId", user.getCompanyId());
		map.put("name", user.getName());
		map.put("password", user.getPassword());
		map.put("nickName", user.getNickName());
		map.put("avatar", user.getAvatar());
		map.put("sex", user.getSex());
		map.put("phone", user.getPhone());
		map.put("email", user.getEmail());
		map.put("idCard", user.getIdCard());
		map.put("birthday", user.getBirthday());
		map.put("deptId", user.getDeptId()+"");
		map.put("state", user.getState());
		getMapParameter(sql, map);
		SqlParameterSource paramSource=new BeanPropertySqlParameterSource(user);
		count = jdbcTemplate.update(sql.toString(), paramSource,keyHolder,new String[]{"id"});
		return keyHolder;
	}

	@Override
	public boolean update(User user, User loginUser) {
		int count = 0;
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set companyId=:companyId ");
		sql.append(", name=:name, nickName=:nickName ");
		sql.append(", avatar=:avatar, sex=:sex, phone=:phone, email=:email, idCard=:idCard ");
		sql.append(", birthday=:birthday, deptId=:deptId ");
		sql.append(", updateUser=:updateUser, updateTime=now() where id=:id");
		map.put("companyId", user.getCompanyId()==null?null:user.getCompanyId());
		map.put("name", user.getName());
		map.put("nickName", user.getNickName());
		map.put("avatar", user.getAvatar());
		map.put("sex", user.getSex());
		map.put("phone", user.getPhone());
		map.put("email", user.getEmail());
		map.put("idCard", user.getIdCard());
        map.put("birthday", user.getBirthday());
		map.put("deptId", user.getDeptId()==null?null:user.getDeptId());
		map.put("updateUser", loginUser.getId());
		map.put("id", user.getId());
		getMapParameter(sql, map);
		count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean delete(User user, User loginUser) {
		int count = 0;
		Map<String, String> map = null;
		if(user.getIds().length()>0){
			for(String ID : user.getIds().split(",")){
				map  = new HashMap<String, String>();
				if(ID==null || ID.equals("")){ break; }
				StringBuffer sql =new StringBuffer("update sys_user set del=1" +
						",updateUser=:updateUser, updateTime=now() " 
						+ " where id =:id ");
				map.put("updateUser", loginUser.getId());
				map.put("id", ID);
				getMapParameter(sql, map);
				count = jdbcTemplate.update(sql.toString(),map);
			}
		}
		return count > 0;
	}

	@Override
    public boolean deleteUserRole(User user){
		int count = 0;
		Map<String, String> map = new HashMap<String, String>();
        StringBuffer sql =new StringBuffer("delete from sys_user_role where " +
                " user_id=:userId ");
        map.put("userId", user.getId());
        getMapParameter(sql, map);
        count = jdbcTemplate.update(sql.toString(),map);
		return count > 0;
	}

    @Override
    public boolean addLoginRecord(LoginRecord loginRecord){
        int count = 0;
        Map<String, String> map  = new HashMap<String, String>();
        StringBuffer sql =new StringBuffer("INSERT INTO sys_login_record  "
                +"( userId, os_name, device, browser_type, ip_address )  ");
        sql.append("values (:userId,:os_name,:device,:browser_type "
                +",:ip_address)");
        map.put("userId", loginRecord.getUserId());
        map.put("os_name", loginRecord.getOsName());
        map.put("device", loginRecord.getDevice());
        map.put("browser_type", loginRecord.getBrowserType());
        map.put("ip_address", loginRecord.getIpAddress());
        getMapParameter(sql, map);
        count = jdbcTemplate.update(sql.toString(), map);
        return count > 0;
    }

    @Override
    public boolean udpatePsw(User user, User loginUser) {
		int count = 0;
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set password=:password ");
		sql.append(", updateUser=:updateUser, updateTime=now() where id=:id");
		map.put("password", user.getPassword());
		map.put("updateUser", loginUser.getId());
		map.put("id", user.getId());
		getMapParameter(sql, map);
		count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

    @Override
    public boolean udpateState(User user, User loginUser) {
		int count = 0;
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sys_user set state=:state ");
		sql.append(", updateUser=:updateUser, updateTime=now() where id=:id");
		map.put("state", user.getState());
		map.put("updateUser", loginUser.getId());
		map.put("id", user.getId());
		getMapParameter(sql, map);
		count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

    @Override
    public String loginRecordList(PageResult<T> page, LoginRecord loginRecord){
        Map<String, String> map  = new HashMap<String, String>();
        RowMapper<LoginRecord> rm = new BeanPropertyRowMapper<LoginRecord>(LoginRecord.class);

        StringBuffer sql = new StringBuffer("select r.id,r.userId," +
                "u.name username,u.nickName,r.os_name osName,"
                +" r.device, r.browser_type browserType,r.ip_address ipAddress, "
                +"r.create_time createTime");
        sql.append(" from sys_login_record r ");
        sql.append(" inner join sys_user u on r.userId=u.id ");

        if(Utils.isNotEmpty(loginRecord)){
			if(Utils.isNotEmpty(loginRecord.getUsername())){
				sql.append(" and instr(u.name,:name)>0 ");
				map.put("name", loginRecord.getUsername());
			}
			if(Utils.isNotEmpty(loginRecord.getDate1()) && Utils.isNotEmpty(loginRecord.getDate2()) ){
				sql.append(" and r.create_time>:date1 and r.create_time<:date2");
				map.put("date1", loginRecord.getDate1());
				map.put("date2", loginRecord.getDate2());
			}
        }
        sql.append(" ORDER BY r.create_time DESC ");
        return jsonPage(getPageQueryList(page, sql.toString(), map, rm));
    }

}
